package com.xu.mb3.jdk;

import com.xu.mybatis.entity.UserInfo;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * 原生JDBC示例
 *
 * @author xuguan
 * @since 2024/11/9
 */
@Slf4j
public class JdbcSample {
    public static void selectByIdSample() {
        final String sql = "select * from mbl_user_info where id = ?";
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            connection = DbUtil.connect();

            pst = connection.prepareStatement(sql);
            pst.setObject(1, 1);
            rs = pst.executeQuery();

            final UserInfo userInfo = new UserInfo();
            while (rs.next()) {
                userInfo.setId(rs.getLong("id"));
                userInfo.setName(rs.getString("name"));
                userInfo.setMobileNo(rs.getString("mobile_no"));
                userInfo.setAge(rs.getString("age"));
                userInfo.setGender(rs.getInt("gender"));
                userInfo.setJob(rs.getString("job"));
                userInfo.setAddress(rs.getString("address"));
                userInfo.setCreateTime(rs.getObject("create_time", LocalDateTime.class));
                userInfo.setUpdateTime(rs.getObject("update_time", LocalDateTime.class));
                userInfo.setDeleted(rs.getInt("deleted"));
            }

            log.info("userInfo={}", userInfo);
        } catch (SQLException e) {
            log.info("获取数据库连接失败", e);
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(connection, pst, rs);
        }
    }

    public static void selectListSample() {
        final String sql = "select * from mbl_user_info";
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            connection = DbUtil.connect();

            pst = connection.prepareStatement(sql);
            rs = pst.executeQuery();

            final List<UserInfo> userInfos = new ArrayList<>();
            while (rs.next()) {
                final UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getLong("id"));
                userInfo.setName(rs.getString("name"));
                userInfo.setMobileNo(rs.getString("mobile_no"));
                userInfo.setAge(rs.getString("age"));
                userInfo.setGender(rs.getInt("gender"));
                userInfo.setJob(rs.getString("job"));
                userInfo.setAddress(rs.getString("address"));
                userInfo.setCreateTime(rs.getObject("create_time", LocalDateTime.class));
                userInfo.setUpdateTime(rs.getObject("update_time", LocalDateTime.class));
                userInfo.setDeleted(rs.getInt("deleted"));
                userInfos.add(userInfo);
            }
            log.info("userInfo={}", userInfos);
        } catch (SQLException e) {
            log.info("获取数据库连接失败", e);
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(connection, pst, rs);
        }
    }

    public static void insertSample() {
        final String sql = "insert into mbl_user_info(name, mobile_no) values (?, ?)";
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            connection = DbUtil.connect();

            pst = connection.prepareStatement(sql);
            pst.setObject(1, "zs");
            pst.setObject(2, "13811112222");
            final int insertCnt = pst.executeUpdate();
            log.info("insertCnt={}", insertCnt);
        } catch (SQLException e) {
            log.info("获取数据库连接失败", e);
            throw new RuntimeException(e);
        } finally {
            DbUtil.close(connection, pst, null);
        }
    }

    public static void main(String[] args) {
        selectByIdSample();
        // insertSample();
        // selectListSample();
    }
}
